>> There is a COPY command that you can use...there is a man page for it,
>> sorry, don't use it myself, so dont know the syntax.
Then some bit about usually using Perl because of trouble getting COPY to
perform exactly right and then having to pay the price with slow inserts
instead of fast COPY (sorry, I overhastily deleted it). I'm pretty sure
Marc posted it (sorry about the cc if it wasn't you Marc)...
Yes I usually have a similar problem, especially with 'buggy' CVS file or
other delimited files that haven't been rigourously generated or with
handling of NULL fields etc.
I clean up the file with Perl but use this code to still use fast COPYs:
#/usr/local/bin/perl5
my $database='test';
open PGSQL, "|psql $database" or die "hey man, you crazy or what! I canny
open pipe psql $database!";
my $table='test';
print PGSQL "COPY $table from stdin;\n"; # First COPY
my $print_count=0; # Set counter to zero
while (<LIST>) { # Where list is a filehandle to your CVS/delimited file
# We go through the file line by line
# Clean-up each line
# And put each element in array @values
# In the order of the fields in the table definition
# And replacing NULLs with '\N' (inclusive of quotes)
print PGSQL join("\t",@values),"\n";
++$print_count;
if (!($print_count%50)) { # every fifty print
print PGSQL "\\.\n"; # close that batch of entries
print PGSQL "COPY $table from stdin;\n"; # start next batch
};
};
print PGSQL "\\.\n";
# we've printed a copy so worst that can happen is we copy in nothing!
# but we must print this at then end to make sure all entries are copied
close(LIST);
close(PGSQL);
I must say that it goes like the proverbial stuff off the shovel.
HTH,
Stuart.
+--------------------------+--------------------------------------+
| Stuart C. G. Rison | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street |
| N.B. new phone code!! | London, W1P 8BT |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM |
| Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk |
+--------------------------+--------------------------------------+